package net.letcode.ct.utils;

import java.security.SecureRandom;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import net.letcode.core.utils.common.StringUtils;
import net.letcode.ct.Main;
import net.letcode.ct.bean.DbField;
import net.letcode.ct.bean.Entity;
import net.letcode.ct.bean.ForeignKey;
import net.letcode.ct.bean.ManyToMany;
import net.letcode.ct.bean.ManyToOne;
import net.letcode.ct.bean.DbField.KeyType;

/**
 * 代码工具类
 * @author zdf
 *
 */
public class CodeUtils {
	
	/** 安全的随机数生成器 */
	private static SecureRandom random = new SecureRandom();
	
	/**
	 * 查询所有表
	 */
	public static String SQL_TABLE_NAMES;
	
	/**
	 * 统计主键的数目
	 */
	public static String SQL_COUNT_PRIMARY_KEY;
	
	/**
	 * 查询表的所有字段
	 */
	public static String SQL_TABLE_FIELDS;
	
	/**
	 * 查询所有外键关联
	 */
	public static String SQL_ALL_FOREIGN_KEY;
	
	/**
	 * 查询多对多中间表的Key
	 */
	public static String SQL_MANY_TO_MANY;
	
	/**
	 * 查询所有实体名
	 * @param dataBaseName
	 * @return
	 */
	public static Map<String, String> getTableNames(final String dataBaseName){
		System.out.println("查询表结构...");
		
		Map<String, String> tableNames = new LinkedHashMap<>();
		
		Connection conn = JdbcUtils.getConn();
		if(conn != null){
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				ps = conn.prepareStatement(SQL_TABLE_NAMES);
				if(ps != null){
					ps.setString(1, dataBaseName);
					rs = ps.executeQuery();
					String tableName, entityComment;
					while(rs.next()){
						tableName = rs.getString("table_name");
						entityComment = rs.getString("table_comment");
						if(entityComment == null)
							entityComment = "";//避免FreeMarker报null错误
						if(checkRealEntity(dataBaseName, tableName))
							tableNames.put(tableName, entityComment);
					}
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				JdbcUtils.close(rs, ps, conn);
			}
		}
		
		return tableNames;
	}
	
	/**
	 * 解析实体类
	 * @param tableName
	 * @param tableComment
	 * @param modules
	 * @param parseTablePrefix
	 * @return
	 */
	public static Entity parseEntity(String tableName, String tableComment, Set<String> modules, Boolean parseTablePrefix) {
		System.out.println("分析：" + tableName);
		
		Entity entity = new Entity();
		entity.setSerialVersionUID(String.valueOf(random.nextLong()));
		entity.setTableName(tableName);
		entity.setEntityComment(tableComment);
		
		if(parseTablePrefix)
			entity.setModuleName(StringUtils.substringBefore(tableName, "_"));
		else
			entity.setModuleName("");
		
		modules.add(entity.getModuleName());
		entity.setEntityName(convertToEntityName(tableName, parseTablePrefix));
		entity.setEntityNameLower(StringUtils.firstCharToLowerCase(entity.getEntityName()));
		
		List<DbField> fields = getFields(tableName);
		entity.setFields(fields);
		
		//判断是否有日期类型
		boolean hasDateType = false;
		for(DbField field : fields){
			if(field.getDataType() == DbField.DateType.Date){
				hasDateType = true;
				break;
			}
		}
		
		//设置主键
		for(DbField field : fields){
			if(field.getKeyType() == KeyType.PrimaryKey){
				entity.setPrimaryKey(field);
				fields.remove(field);
				break;
			}
		}
		
		entity.setHasDateType(hasDateType);
		
		return entity;
	}
	
	/**
	 * 解析外键关联
	 * @param entitys
	 * @param dataBaseName
	 * @return
	 */
	public static void parseForeignKey(List<Entity> entitys, String dataBaseName){
		System.out.println("关联所有外键...");
		
		Map<String, Entity> entityMap = new HashMap<String, Entity>();
		for(Entity entity : entitys)
			entityMap.put(entity.getTableName(), entity);
		
		List<ForeignKey> foreignKeys = new ArrayList<>();
		Connection conn = JdbcUtils.getConn();
		if(conn != null){
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				ps = conn.prepareStatement(SQL_ALL_FOREIGN_KEY);
				if(ps != null){
					ps.setString(1, dataBaseName);
					rs = ps.executeQuery();
					while(rs.next()){
						foreignKeys.add(new ForeignKey(
								rs.getString("source_column"), 
								rs.getString("source_table"), 
								rs.getString("target_table")));
					}
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				JdbcUtils.close(rs, ps, conn);
			}
			
			Entity sourceEntity;
			Entity targetEntity;
			ManyToOne mto = null;
			Set<String> mtmTableSet = new HashSet<String>();
			for(ForeignKey foreignKey : foreignKeys){
				sourceEntity = entityMap.get(foreignKey.getSourceTableName());
				if(sourceEntity != null){//排除多对多关联
					targetEntity = entityMap.get(foreignKey.getTargetTableName());
					if(targetEntity != null){
						//生成一对多
						if(Main.buildOneToMany){
							targetEntity.getChildren().add(sourceEntity);
							targetEntity.getChildMap().put(sourceEntity.getEntityName(), foreignKey.getColumnName());
						}
						
						//生成多对一
						if(Main.buildManyToOne){
							mto = new ManyToOne(targetEntity.getTableName(), foreignKey.getColumnName(), targetEntity.getModuleName(), targetEntity.getEntityName(), targetEntity.getEntityNameLower());
							mto.setEntityComment(targetEntity.getEntityComment());
							sourceEntity.getManyToOnes().add(mto);
						}
					}
				}else
					mtmTableSet.add(foreignKey.getSourceTableName());
			}
			
			//生成多对多
			if(Main.buildManyToMany){
				Iterator<String> iterator = mtmTableSet.iterator();
				while (iterator.hasNext())
					setManyToMany(entityMap, dataBaseName, iterator.next());
			}
		}
	}
	
	/**
	 * 设置多对多关联
	 * @param entityMap
	 * @param joinTableName
	 */
	private static void setManyToMany(Map<String, Entity> entityMap, String dataBaseName, String joinTableName){
		
		String[][] tableArray = new String[2][2];
		Connection conn = JdbcUtils.getConn();
		if(conn != null){
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				ps = conn.prepareStatement(SQL_MANY_TO_MANY);
				if(ps != null){
					ps.setString(1, dataBaseName);
					ps.setString(2, joinTableName);
					rs = ps.executeQuery();
					int i = 0;
					while(rs.next()){
						tableArray[i][0] = rs.getString("table_name");
						tableArray[i][1] = rs.getString("column_name");
						i++;
					}
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				JdbcUtils.close(rs, ps, conn);
			}
			
			ManyToMany mtm;
			Entity targetEntity;
			Entity sourceEntity = entityMap.get(tableArray[0][0]);
			if(sourceEntity != null){
				targetEntity = entityMap.get(tableArray[1][0]);
				if(targetEntity != null){
					//开始正向关联
					mtm = new ManyToMany(
							dataBaseName,
							joinTableName,
							targetEntity.getTableName(),
							targetEntity.getModuleName(),
							targetEntity.getEntityName(),
							targetEntity.getEntityNameLower(),
							targetEntity.getEntityComment(),
							tableArray[0][1], 
							tableArray[1][1]);
					sourceEntity.getMtms().add(mtm);
					
					//开始反向关联
					mtm = new ManyToMany(
							dataBaseName,
							joinTableName,
							sourceEntity.getTableName(),
							sourceEntity.getModuleName(),
							sourceEntity.getEntityName(),
							sourceEntity.getEntityNameLower(),
							sourceEntity.getEntityComment(),
							tableArray[1][1], 
							tableArray[0][1]);
					targetEntity.getMtms().add(mtm);
				}
			}
		}
		
	}

	/**
	 * 检查表是否为实体映射表，排除多对多的关联表
	 * @param dataBaseName
	 * @param tableName
	 * @return
	 */
	private static boolean checkRealEntity(String dataBaseName, String tableName){
		long count = JdbcUtils.queryLong(SQL_COUNT_PRIMARY_KEY, dataBaseName, tableName);
		return count == 1l;
	}
	
	/**
	 * 表名转换成实体名
	 * @param fullTableName
	 * @param parseTablePrefix
	 * @return
	 */
	private static String convertToEntityName(String fullTableName, Boolean parseTablePrefix){
		String tableName = fullTableName;
		if(parseTablePrefix)
			tableName = StringUtils.substringAfter(fullTableName, "_");
		
		StringBuffer entityName = new StringBuffer();
		String[] names = tableName.split("_");
		for(String name : names){
			entityName.append(StringUtils.firstCharToUpperCase(name));
		}
		
		return entityName.toString();
	}
	
	/**
	 * 表名转换成实体名
	 * @param fieldName
	 * @return
	 */
	private static String convertToFieldName(String fieldName){
		StringBuffer entityName = new StringBuffer();
		String[] names = fieldName.split("_");
		
		boolean firstName = true;
		for(String name : names){
			if(firstName){
				entityName.append(name);
				firstName = false;
			}else{
				entityName.append(StringUtils.firstCharToUpperCase(name));
			}
		}
		
		return entityName.toString();
	}
	
	/**
	 * 获取实体的属性
	 * 注意：对于唯一索引，需要将其Index_type设置为UNIQUE
	 * @param tableName
	 * @return
	 */
	private static List<DbField> getFields(String tableName){
		List<DbField> fields = new ArrayList<DbField>();
		DbField field = null;
				
		Connection conn = JdbcUtils.getConn();
		if(conn != null){
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				ps = conn.prepareStatement(SQL_TABLE_FIELDS + tableName);
				if(ps != null){
					rs = ps.executeQuery();
					while(rs.next()){
						String str = rs.getString("Field");
						field = new DbField();
						field.setTableFieldName(str);
						field.setFieldName(convertToFieldName(str));
						field.setFieldNameFirstUpper(StringUtils.firstCharToUpperCase(field.getFieldName()));
						
						str = rs.getString("Type");
						if (str.contains("varchar")) {
							field.setDataType(DbField.DateType.String);
							field.setLength(Integer.valueOf(StringUtils.substringBetween(str, "(", ")")));
						} else if (str.contains("text")) {
							field.setDataType(DbField.DateType.String);
							field.setLength(65535);
						} else if (str.contains("bigint(20)")) {
							field.setDataType(DbField.DateType.Long);
						} else if (str.contains("int(11)")) {
							field.setDataType(DbField.DateType.Integer);
						} else if (str.contains("smallint(6)")) {
							field.setDataType(DbField.DateType.Short);
						} else if (str.contains("tinyint(4)")) {
							field.setDataType(DbField.DateType.Byte);
						} else if (str.contains("tinyint(1)")) {
							field.setDataType(DbField.DateType.Boolean);
						} else if (str.contains("double")) {
							field.setDataType(DbField.DateType.Double);
						} else if (str.contains("decimal")) {
							field.setDataType(DbField.DateType.Double);
						} else if (str.contains("float")) {
							field.setDataType(DbField.DateType.Float);
						} else if (str.equals("datetime") || str.contains("timestamp")) {
							field.setDataType(DbField.DateType.Date);
							field.setLength(19);
						}else if (str.equals("date")) {
							field.setDataType(DbField.DateType.Date);
							field.setLength(10);
						}else if (str.equals("time")) {
							field.setDataType(DbField.DateType.Date);
							field.setLength(8);
						}
						
						str = rs.getString("Null");
						field.setNotNull(str.equals("NO"));
						
						field.setDefaultValue(rs.getString("Default"));
						field.setComment(rs.getString("Comment"));
						
						str = rs.getString("Key");
						if(StringUtils.isBlank(str) || str.equals("UNI")){ // 放行普通字段及唯一索引
							field.setKeyType(DbField.KeyType.NotKey);
							fields.add(field);
						}else if(str.equals("PRI")){ // 处理主键
								field.setKeyType(DbField.KeyType.PrimaryKey);
								fields.add(field);
						}else if(str.equals("MUL")) // 处理外键
								field.setKeyType(DbField.KeyType.Foreignkey);
					}
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				JdbcUtils.close(rs, ps, conn);
			}
		}
		
		return fields;
	}
}
